package com.boby.homecamera.db

import android.content.ContentValues
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import androidx.core.database.getLongOrNull

class VideoTable(val database:SQLiteDatabase) {


     companion object {
         private   val TABLE_VIDEO = "video"
         fun createTable(db: SQLiteDatabase) {

             db.execSQL(
                 """
            CREATE TABLE IF NOT EXISTS $TABLE_VIDEO (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                width INTEGER NOT NULL,
                height INTEGER NOT NULL,
                path TEXT NOT NULL,
                start_time INTEGER ,
                end_time INTEGER ,
                length INTEGER 
            );
            """.trimIndent()
             )
         }
     }

    // --- 插入 Video 数据 ---
    fun insertVideo(video: Video): Long {
        val values = ContentValues().apply {
            put("width", video.width)
            put("height", video.height)
            put("start_time", video.startTime)
            put("path", video.path)
            put("end_time", video.endTime)
            put("length", video.length)
        }
        return database.insert(TABLE_VIDEO, null, values)
    }

    // --- 插入 Video 数据 ---
    fun updateVideo(video: Video): Int {
        val values = ContentValues().apply {
            put("width", video.width)
            put("height", video.height)
            put("path", video.path)
            put("start_time", video.startTime)
            put("end_time", video.endTime)
            put("length", video.length)
        }
        return database.update(TABLE_VIDEO, values, "id=?", arrayOf(video.id.toString()))
    }


    // --- 查询所有 Video 数据 ---
    fun queryAllVideos(): List<Video> {
        val videos = mutableListOf<Video>()
        val cursor: Cursor = database.query(
            TABLE_VIDEO,
            arrayOf("id", "width", "height", "path","start_time", "end_time", "length"),
            null, null, null, null, null
        )
        cursor.use {
            while (it.moveToNext()) {
                val video = Video(
                    id = it.getLong(it.getColumnIndexOrThrow("id")),
                    width = it.getInt(it.getColumnIndexOrThrow("width")),
                    height = it.getInt(it.getColumnIndexOrThrow("height")),
                    path = it.getString(it.getColumnIndexOrThrow("path")),
                    startTime = it.getLongOrNull(it.getColumnIndexOrThrow("start_time")),
                    endTime =  it.getLongOrNull(it.getColumnIndexOrThrow("end_time")),
                    length = it.getLongOrNull(it.getColumnIndexOrThrow("length"))
                )
                videos.add(video)
            }
        }
        return videos
    }
    // --- 查询所有 Video 数据 --- 23:59 00:00:02
    fun queryVideos(startTime:Long?,endTime:Long?): List<Video> {
        val videos = mutableListOf<Video>()
        val cursor: Cursor = database.query(
            TABLE_VIDEO,
            arrayOf("id", "width", "height", "path","start_time", "end_time", "length"),
            "end_time >= ? AND start_time <= ? AND length > 0",  arrayOf(startTime.toString(),endTime.toString()),
            null,
            null,
            "start_time ASC"
        )
        cursor.use {
            while (it.moveToNext()) {
                val video = Video(
                    id = it.getLong(it.getColumnIndexOrThrow("id")),
                    width = it.getInt(it.getColumnIndexOrThrow("width")),
                    height = it.getInt(it.getColumnIndexOrThrow("height")),
                    path = it.getString(it.getColumnIndexOrThrow("path")),
                    startTime = it.getLongOrNull(it.getColumnIndexOrThrow("start_time")),
                    endTime =  it.getLongOrNull(it.getColumnIndexOrThrow("end_time")),
                    length = it.getLongOrNull(it.getColumnIndexOrThrow("length"))
                )
                videos.add(video)
            }
        }
        return videos
    }

    fun queryLast(): Video? {
// "path IS NOT NULL AND path != '' AND start_time IS NOT NULL AND end_time IS NOT NULL AND length > 0",
        val cursor: Cursor = database.query(
            TABLE_VIDEO,
            arrayOf("id", "width", "height", "path","start_time", "end_time", "length"),
            "path IS NOT NULL AND length > 0",
            null, null, null,
            "start_time DESC", // 可选：按时间倒序拿最近的
            "1" // 只取一条
        )
        cursor.use {
            while (it.moveToFirst()) {
                val video = Video(
                    id = it.getLong(it.getColumnIndexOrThrow("id")),
                    width = it.getInt(it.getColumnIndexOrThrow("width")),
                    height = it.getInt(it.getColumnIndexOrThrow("height")),
                    path = it.getString(it.getColumnIndexOrThrow("path")),
                    startTime = it.getLongOrNull(it.getColumnIndexOrThrow("start_time")),
                    endTime =  it.getLongOrNull(it.getColumnIndexOrThrow("end_time")),
                    length = it.getLongOrNull(it.getColumnIndexOrThrow("length"))
                )
                return video
            }
        }
        return null
    }

    data class Video(
        var id: Long = -1,
        var width: Int = 0,
        var height: Int = 0,
        var path: String = "",
        var startTime: Long? = null,
        var endTime: Long? = null,
        var length: Long? = null
    )
}


